package com.yyy.dao;

import com.yyy.entity.Config;
import com.yyy.entity.Record;
import com.yyy.util.DBUtil;
import com.yyy.util.DateUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author：yyy
 * @Package：com.yyy.dao
 * @Project：HuTuBiill
 * @name：RecordDao
 * @Date：2024/11/11 21:42
 * @Filename：RecordDao
 */
public class RecordDao {
    /*
     * 添加
     * */
    public void add(Record record){
        String sql="insert into record values(null,?,?,?,?)";

        try {
            Connection c= DBUtil.getConnection();
            PreparedStatement ps=c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1,record.spend);
            ps.setInt(2,record.cid);
            ps.setString(3,record.Comment);
            ps.setDate(4, DateUtil.UtilDateToSqlDate(record.date));
            ps.execute();
            ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                int id=rs.getInt(1);
                record.id=id;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


    }

    /*
     *
     * 删除
     * */

    public void delete(int id){
        try {
            String sql="delete from record where id="+id;
            Connection c= DBUtil.getConnection();
            Statement ps=c.createStatement();
            ps.execute(sql);


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /*
     *
     * 更新
     * */

    public void update (Record record){
        String sql="update record set spend=?,cid=?,comment_=?,date_=? where id=?";
        try {
            Connection c= DBUtil.getConnection();
            PreparedStatement ps=c.prepareStatement(sql);
            ps.setInt(1,record.spend);
            ps.setInt(2,record.cid);
            ps.setString(3,record.Comment);
            ps.setDate(4, DateUtil.UtilDateToSqlDate(record.date));

            ps.execute();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    /*
     * 根据id查询
     * */

    public Record selectById(int id){
        Record record=null;
        try {
            String sql="select * from record where id="+id;
            Connection c= DBUtil.getConnection();
            Statement ps=c.createStatement();
            ResultSet rs=ps.executeQuery(sql);
            if (rs.next()){
                record=new Record();
                record.id=id;
                record.spend=rs.getInt("spend");
                record.cid=rs.getInt("cid");
                record.Comment=rs.getString("comment_");
                record.date=rs.getDate("date_");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return record;
    }



    /*
     * 分页查询
     * */
    public List<Record> list(int start, int count){
        List<Record> records=new ArrayList<>();
        String sql="select * from record order by id desc limit ?,?";
        try {
            Connection connection=DBUtil.getConnection();
            PreparedStatement ps= connection.prepareStatement(sql);
            ps.setInt(1,start);
            ps.setInt(2,count);
            ResultSet rs= ps.executeQuery();
            while (rs.next()){
                Record record=new Record();
                record.id=rs.getInt("id");
                record.spend=rs.getInt("spend");
                record.cid=rs.getInt("cid");
                record.Comment=rs.getString("comment_");
                record.date=rs.getDate("date_");
                records.add(record);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return records;
    }

    /*
     *
     * 查询全部
     * */

    public List<Record> list(){
        return list(0,Short.MAX_VALUE);
    }

    /*
    * 根据cid进行查询
    * */
    public List<Record> selectByCId(int cid){
        List<Record> records=new ArrayList<>();
        try {
            String sql="select * from record where id="+cid;
            Connection c= DBUtil.getConnection();
            Statement ps=c.createStatement();
            ResultSet rs=ps.executeQuery(sql);
            if (rs.next()){
                Record record=new Record();
                record.id=rs.getInt("id");
                record.spend=rs.getInt("spend");
                record.cid=cid;
                record.Comment=rs.getString("comment_");
                record.date=rs.getDate("date_");
                records.add(record);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return records;
    }
    /*
    * 获取某天的查询结果
    * */
    public List<Record> selectByDate(Date date){
        List<Record> records=new ArrayList<>();
        String sql="select * from record where date_=?";
        try {
            Connection connection=DBUtil.getConnection();
            PreparedStatement ps= connection.prepareStatement(sql);
            ps.setDate(1,DateUtil.UtilDateToSqlDate(date));

            ResultSet rs= ps.executeQuery();
            while (rs.next()){
                Record record=new Record();
                record.id=rs.getInt("id");
                record.spend=rs.getInt("spend");
                record.cid=rs.getInt("cid");
                record.Comment=rs.getString("comment_");
                record.date=rs.getDate("date_");
                records.add(record);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return records;
    }

    /*
    * 获取今天的消费记录
    * */
    public List<Record> listToday(){
        return selectByDate(DateUtil.today());
    }

    /*
    * 获取一段日期的消费记录
    * */
    public List<Record> selectByDate(Date startDate,Date endDate){
        List<Record> records=new ArrayList<>();
        String sql="select * from record where date_>=? and date_ <=?";
        try {
            Connection connection=DBUtil.getConnection();
            PreparedStatement ps= connection.prepareStatement(sql);
            ps.setDate(1,DateUtil.UtilDateToSqlDate(startDate));
            ps.setDate(2,DateUtil.UtilDateToSqlDate(endDate));
            ResultSet rs= ps.executeQuery();
            while (rs.next()){
                Record record=new Record();
                record.id=rs.getInt("id");
                record.spend=rs.getInt("spend");
                record.cid=rs.getInt("cid");
                record.Comment=rs.getString("comment_");
                record.date=rs.getDate("date_");
                records.add(record);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return records;
    }

    /*
    * 获取本月的消费记录
    * */
    public List<Record> listMonth(){
        return selectByDate(DateUtil.monthBegin(),DateUtil.monthEnd());
    }


    /*
     * 查询总笔数
     * */
    public int getTotal(){
        int total=0;
        try {
            String sql="select count(1) from record" ;
            Connection connection=DBUtil.getConnection();
            Statement ps= connection.createStatement();
            ResultSet resultSet = ps.executeQuery(sql);
            while (resultSet.next()){
                total=resultSet.getInt(1);
            }
            System.out.println("total:"+total);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return total;
    }



}
